﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Configuration;
using System.Data.SqlClient;

namespace Forex.IB.RealTimeBars.Components
{
    public class SqlHelper
    {
        #region Common

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetTickers()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("[dbo].[sp_GetTickers]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker_Id", 0);
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public void ModifyTickers(string Ticker, string Description)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("[dbo].[sp_AddTickers]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker", Ticker);
                        command.Parameters.AddWithValue("@Description", Description);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

        }

        public void ModifyTickers()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("[dbo].[sp_AddTickers]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

        }
        #endregion

        #region Interactive Brokers
        /// <summary>
        /// 
        /// </summary>
        /// <param name="RequestId"></param>
        /// <returns></returns>
        public int GetTickerId(string Ticker)
        {
            int tickerid = 0;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetTickerId", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        SqlParameter parameter = command.Parameters.Add("@TickerId", SqlDbType.Int);
                        command.Parameters.AddWithValue("@Ticker", Ticker);
                        parameter.Direction = ParameterDirection.Output;
                        command.ExecuteNonQuery();
                        tickerid = Convert.ToInt32(parameter.Value);
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return tickerid;
        }

        public void AddRealTimeBarsData(int Ticker_Id, DateTime dt, double Open, double Close, double High, double Low, long Volume)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddRealTimeBarsData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker_Id", Ticker_Id);
                        command.Parameters.AddWithValue("@DateTime", dt);
                        command.Parameters.AddWithValue("@Open", Open);
                        command.Parameters.AddWithValue("@Close", Close);
                        command.Parameters.AddWithValue("@High", High);
                        command.Parameters.AddWithValue("@Low", Low);
                        command.Parameters.AddWithValue("@Volume", Volume);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void AddHistoricalData(int Ticker_Id, DateTime dt, double Open, double Close, double High, double Low, long Volume)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddHistoricalData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker_Id", Ticker_Id);
                        command.Parameters.AddWithValue("@DateTime", dt);
                        command.Parameters.AddWithValue("@Open", Open);
                        command.Parameters.AddWithValue("@Close", Close);
                        command.Parameters.AddWithValue("@High", High);
                        command.Parameters.AddWithValue("@Low", Low);
                        command.Parameters.AddWithValue("@Volume", Volume);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }
        public void AddIchimokuIndicators(int Ticker_Id, DateTime dt, double Open, double Close, double High, double Low, long Volume)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddIchimokuIndicators", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker_Id", Ticker_Id);
                        command.Parameters.AddWithValue("@DateTime", dt);
                        command.Parameters.AddWithValue("@Open", Open);
                        command.Parameters.AddWithValue("@Close", Close);
                        command.Parameters.AddWithValue("@High", High);
                        command.Parameters.AddWithValue("@Low", Low);
                        command.Parameters.AddWithValue("@Volume", Volume);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void AddOrderRequest(string Ticker, string ActionSide, decimal Price, int Quantity, int Order_RefId, decimal Pip,string orderType)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddOrderRequest", connection))
                    {

                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker", Ticker);
                        command.Parameters.AddWithValue("@ActionSide", ActionSide);
                        command.Parameters.AddWithValue("@Price", Price);
                        command.Parameters.AddWithValue("@Quantity", Quantity);
                        command.Parameters.AddWithValue("@Order_RefId", Order_RefId);
                        command.Parameters.AddWithValue("@Pip", Pip);
                        command.Parameters.AddWithValue("@OrderType", orderType);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void AddMessage(string Message)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_AddLog", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Message", Message);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public int GetOrderByOrderIdToCancel(int orderId)
        {
            int ret=0;
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetOrderByOrderIdToCancel", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", orderId);
                        ret = (Int32)command.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
            return ret;
        }




        public bool IsHistoricalDataLoaded(DateTime dateTime)
        {
            bool isLoaded = false;
            try
            {
                var sqldate = String.Format("{0:M/d/yyyy HH:mm:ss}", dateTime);
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_IsHistoricalDataLoaded", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        SqlParameter parameter = command.Parameters.Add("@dateTime", SqlDbType.DateTime);
                        parameter.Value = sqldate;
                        isLoaded = (bool)command.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
            return isLoaded;
        }

        public void MakeHistoricalData()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_MakeHistoricalData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandTimeout = 300;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void MakeIchimokuIndicators()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_MakeIchimokuIndicators", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void MakeIchimokuIndicatorsFrmHistoricalDataCriteria()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_MakeIchimokuIndicators_Hist_Criteria", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandTimeout = 300;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
                throw ex;
            }
            finally
            {

            }
        }


        public DataTable LastUpdated()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_LastUpdate", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }


        public DataTable DisplayOrders()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_DisplayOrders", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }


        public DataTable GetOrderByOrderId(int OrderId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetOrderByOrderId", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }


        public DataTable GetListUnprocessedTradeNotify()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetListUnprocessedTradeNotify", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetListUnprocessedTriggerNotify()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetListUnprocessedTriggerNotify", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetHistoricalData()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetHistoricalData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetRealTimeBarsData()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_RealTimeBarsData", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }



        public DataTable GetIchimokuIndicatorsToArchive()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetIchimokuIndicators", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetCurrentBO()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_CurrentBO", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetListUnprocessedOrders()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetListUnprocessedOrders", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }
        public void ResetIchimokuIndicators()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_ResetIchimokuIndicators", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void UpdateProcess(bool IsLoaded)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateProcess", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@IsLoaded", IsLoaded);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }


        public void UpdateTickersIn(string Ticker, bool In)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateTickers_In", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker", Ticker);
                        command.Parameters.AddWithValue("@IN", In);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }


        public void UpdateTradeNotify(int OrderId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateTradeNotify", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }


        public void UpdateTriggerNotify(int Id)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateTriggerNotify", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", Id);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }


        public void UpdateOrderStatus(int OrderId, string Status, string Remaining, decimal AverageFillPrice, decimal LastFillPrice, string WhyHeld)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateOrderStatus", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        command.Parameters.AddWithValue("@Status", Status);
                        command.Parameters.AddWithValue("@Remaining", Remaining);
                        command.Parameters.AddWithValue("@AverageFillPrice", AverageFillPrice);
                        command.Parameters.AddWithValue("@LastFillPrice", LastFillPrice);
                        command.Parameters.AddWithValue("@WhyHeld", WhyHeld);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }

        public void UpdateOrderExecutionStarted(int OrderId, bool IsStarted)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_UpdateOrderStarted", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@IsStarted", IsStarted);
                        command.Parameters.AddWithValue("@OrderId", OrderId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }
        #endregion

        #region DataMangement


        public void StartUpRoutine()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_StartUpRoutine", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetTradeInOutByTicker(string Ticker)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_TradeInOutByTicker", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker", Ticker);
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetLastTradeInOutByTicker(string Ticker)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_LastTradeInOutByTicker", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Ticker", Ticker);
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetHistoricalLogData()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetLog", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GetListOrdersToBeCancelled()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetListOrdersToBeCancelled", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        using (IDataReader reader = command.ExecuteReader())
                        {
                            dt.Load(reader);
                            reader.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        #endregion

        #region Reports
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>

        public DataTable GetIChimomuIndicators(DateTime from, DateTime to)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("dbo.sp_GetIchimokuIndicatorsfrmDateTime", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@DateTimeFrom", from);
                        command.Parameters.AddWithValue("@DateTimeTo", to);
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GForexorexRadar_Wireframe()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("SELECT *FROM [dbo].[ForexRadar_Wireframe]", connection))
                    {
                        command.CommandType = CommandType.Text;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        public DataTable GForexorexReport_Last4Hour()
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(Common.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("SELECT *FROM dbo.IChimokuIndicators_Last4hour", connection))
                    {
                        command.CommandType = CommandType.Text;
                        IDataReader reader = command.ExecuteReader();
                        dt.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ForexException Forexex = new ForexException("Error occured for SqlHelper" + " " + ex.Message.ToString() + ". Debug Information: " + System.Reflection.MethodInfo.GetCurrentMethod().Name);
                ErrorHandler.WriteException(Forexex);
            }
            finally
            {

            }

            return dt;
        }

        #endregion
    }
}
